/*********************************************************************************
 *      Copyrightt:  (C) 2025 LingYun<lingyun@email.com>
 *                  All rights reserved.
 *
 *       Filename:  database.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(15/03/25)
 *         Author:  LingYun <lingyun@email.com>
 *      ChangeLog:  1, Release initial version on "15/03/25 17:06:13"
 *                 
 ********************************************************************************/

#include "database.h"

static sqlite3         *s_serdb = NULL;

static int callback(void *notused, int argc, char **argv, char **azcolname)
{
	(void)notused;
	int i;

	if(argc > 0 && (argv == NULL || azcolname == NULL))
	{
		log_error("NULL argv or azColName in callback\n");
		return -1;
	}

	for(i=0; i<argc; i++)
	{
		log_info("%s = %s\n", azcolname[i], argv[i] ? argv[i] : "NULL");
	}
	return 0;
}


int database_init(const char *db)     
{
	char       *errmsg = NULL;
	char       buf[256];

	if( !db )
	{
		log_error("Invalid input arguments\n");
		return -1;
	}

	if( 0==access(db, F_OK) )   /*检查数据库存不存在 */
	{
		if( SQLITE_OK != sqlite3_open(db, &s_serdb) )
		{
			log_error("open database file '%s' failure\n", db);
			return -2;
		}
		log_info("open database file '%s' ok\n", db);
		return 0;
	}

	if( SQLITE_OK != sqlite3_open(db, &s_serdb) )   /*不存在就创建*/
	{
		log_error("create database file '%s' failure\n", db);
		return -2;
	}

	/*SQLite 在将数据传递给操作系统后，不会等待数据真正写入磁盘就会继续执行 */
	sqlite3_exec(s_serdb, "pragma synchronous = OFF; ", NULL, NULL, NULL);

	/*auto_vacuum 用于控制数据库的自动清理机制。将 auto_vacuum 设置为 2 意味着开启 增量式自动清理*/
	sqlite3_exec(s_serdb, "pragma auto_vacuum = 2 ; ", NULL, NULL, NULL);

	/*创建表 */
	memset(buf, 0, sizeof(buf));
	snprintf(buf, sizeof(buf),
			"CREATE TABLE IF NOT EXISTS `%s` ("
			"SN TEXT NOT NULL, "
			"DATIME TEXT NOT NULL, "
			"TEMPERATURE REAL"
			");",
			TABLENAME);  /*  反引号包裹表名防止特殊字符问题*/

	if( SQLITE_OK != sqlite3_exec(s_serdb, buf, callback, 0, &errmsg) )
	{
		log_error("create data_table in database file '%s' failure: %s\n", db, errmsg);
		sqlite3_free(errmsg);
		sqlite3_close(s_serdb);
		unlink(db);
		return -4;
	}

	log_info("create and init database file '%s' ok\n", db);
	return 0;
}


/*关闭数据库并记录一条警告*/
void database_term(void)
{
	log_warn("close sqlite database now\n");
	sqlite3_close(s_serdb);

	return ;
}


/*插入数据*/
int database_insert(data_t *data)
{
	char			buf[256];
	char 			*errmsg = 0;

	memset(buf, 0, sizeof(buf));
	snprintf(buf, sizeof(buf), "INSERT INTO %s(SN, DATIME, TEMPERATURE)VALUES('%s', '%s', '%f');", TABLENAME, data->sn, data->time, data->temper);
	/* */
	log_info("buf:%s\n", buf);

	if(SQLITE_OK != sqlite3_exec(s_serdb, buf, callback, 0, &errmsg))
	{
		log_error("insert table error:%s\n", errmsg);
		sqlite3_free(errmsg);
		return -1;
	}
	else
	{
		log_info("insert table successfully\n");
		return 0;
	}
}


// 插入前检查唯一性
int database_safe_insert(socket_ser *sock, data_t *data) 
{
	sqlite3_stmt   *stmt;
	int            exists = 0;
	char           *check_sql = "SELECT 1 FROM `client_table` WHERE SN=? AND DATIME=?";

	if ( !data->sn || !data->time )
	{
		log_error("Invalid input pointers\n");
		return -1;
	}

	if(sqlite3_prepare_v2(s_serdb, check_sql, -1, &stmt, NULL) != SQLITE_OK) 
	{
		log_error("Failed to prepare check statement\n");
		return -1;
	}

	if(sqlite3_bind_text(stmt, 1, data->sn, -1, SQLITE_STATIC) != SQLITE_OK)   /*预处理语句 */
	{
		log_error("Failed to bind sn.\n");
		sqlite3_finalize(stmt);
		return -1;
	}

	if(sqlite3_bind_text(stmt, 2, data->time, -1, SQLITE_STATIC) != SQLITE_OK)
	{
		log_error("Failed to bind datime\n");
		sqlite3_finalize(stmt);
		return -1;
	}

	if (sqlite3_step(stmt) == SQLITE_ROW) 
	{
		exists = 1;
	}
	sqlite3_finalize(stmt);

	if (!exists) 
	{
		return database_insert(data);
	}
	else
	{
		log_warn("Duplicate record, skip insertion\n");
		return 1;
	}
}


int database_check_write_delete(socket_ser *sock, data_t *data) 
{
	char            buf[256];
	char            **dbresult = NULL;
	int             row = 0;       /*行*/
	int             column = 0;    /*列*/
	char            *errmsg = NULL;
	int             rv;
	sqlite3_stmt    *stmt;
	const char      *rowid_str;

	if(sqlite3_exec(s_serdb, "BEGIN IMMEDIATE;", NULL, NULL, &errmsg) != SQLITE_OK)	/*开启事务保证原子性 */
	{
		log_error("Failed to begin transaction: %s", errmsg);
		sqlite3_free(errmsg);
		return -1;
	}

	/*第一步：查询数据并发送 */
	memset(buf, 0, sizeof(buf));
	snprintf(buf, sizeof(buf), "SELECT ROWID,DATIME, TEMPERATURE FROM `%s` ORDER BY ROWID ASC LIMIT 1;", TABLENAME);    /*显式选择ROWID保证获取正确的行标识,按ROWID 升序排列（从小到大），第一条即为最早插入的数据。LIMIT 1：仅返回一条数据。*/

	rv = sqlite3_get_table(s_serdb, buf, &dbresult, &row, &column, &errmsg);
	if (rv != SQLITE_OK) 
	{
		log_error("SELECT error: %s\n", errmsg);
		sqlite3_free(errmsg);
		sqlite3_exec(s_serdb, "ROLLBACK;", NULL, NULL, NULL);
		return -1;   /*查询失败*/
	}

	if (row < 1) /* 无数据可处理*/ 
	{
		log_info("No data in table\n");
		sqlite3_free_table(dbresult);
		sqlite3_exec(s_serdb, "COMMIT;", NULL, NULL, NULL); /*提交空事务*/
		return 0; 
	}

	rowid_str = dbresult[column + 0];  // 第一列是唯一标识符,ROWID

	if (rowid_str == NULL)
	{
		log_error("Invalid rowid\n");
		sqlite3_free_table(dbresult);
		sqlite3_exec(s_serdb, "ROLLBACK;", NULL, NULL, NULL);
		return -1;
	}

	sqlite3_int64 rowid = atoll(rowid_str);

	if (column < 3)
	{
		log_error("Insufficient columns in query result\n");
		sqlite3_free_table(dbresult);
		sqlite3_exec(s_serdb, "ROLLBACK;", NULL, NULL, NULL);
		return -2;
	}

	memset(buf, 0, sizeof(buf));
	if(snprintf(buf,sizeof(buf), "%s/%s/%f\n", data->sn, dbresult[column + 1], atof(dbresult[column + 2])) < 0)  
	{
		log_error("Failed to format data\n");
		sqlite3_free_table(dbresult);
		sqlite3_exec(s_serdb, "ROLLBACK;", NULL, NULL, NULL);
		return -2;
	}

	/*第二步：发送数据*/
	rv = write(sock->sockfd, buf, strlen(buf));	
	if (rv < 0) 
	{
		log_error("Write failed: %s\n", strerror(errno));
		close(sock->sockfd);
		sqlite3_free_table(dbresult);
		sqlite3_exec(s_serdb, "ROLLBACK;", NULL, NULL, NULL);
		return -2;  /*写入失败*/
	}

	/*第三步：删除这行数据 */
	const char *delete_sql = "DELETE FROM `" TABLENAME "` WHERE ROWID = ?;";

	if (sqlite3_prepare_v2(s_serdb, delete_sql, -1, &stmt, NULL) != SQLITE_OK)
	{
		log_error("Prepare failed: %s", sqlite3_errmsg(s_serdb));
		sqlite3_free_table(dbresult);
		sqlite3_exec(s_serdb, "ROLLBACK;", NULL, NULL, NULL);
		return -3;
	}

	sqlite3_bind_int64(stmt, 1, rowid);
	rv = sqlite3_step(stmt);
	sqlite3_finalize(stmt);

	if (rv != SQLITE_DONE || sqlite3_changes(s_serdb) != 1)
	{
		log_error("DELETE failed: %s\n", sqlite3_errmsg(s_serdb));
		sqlite3_free_table(dbresult);
		sqlite3_exec(s_serdb, "ROLLBACK;", NULL, NULL, NULL);
		return -3;
	}

	if (sqlite3_exec(s_serdb, "COMMIT;", NULL, NULL, &errmsg) != SQLITE_OK)
	{
		log_error("COMMIT failed: %s\n", errmsg);
		sqlite3_free(errmsg);
		return -4;
	}

	sqlite3_free_table(dbresult);
	return 1;  /*成功处理一条*/
}

